<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use app\admin\model\Project;
use think\Db;
use Exception;
use think\exception\DbException;
use think\exception\PDOException;
use think\exception\ValidateException;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use Elasticsearch\ClientBuilder;

/**
 * 人材机库管理
 *
 * @icon fa fa-circle-o
 */
class ProjectRencaiji extends Backend
{

    /**
     * ProjectRencaiji模型对象
     * @var \app\admin\model\ProjectRencaiji
     */
    protected $model = null;

    protected $projectConfig = null;
    protected $searchFields = 'name,admin.nickname';
    protected $noNeedRight = ['import_preview', 'catelist', 'getListByProject'];

    protected $excelTitleMapping = [
        'name' => ['名称、型号、规格', '名称', '材料名称', '名称及规格', '名  称', '材 料 名 称', '名称(材料、机械规格型号)'],
        'code' => ['编号', '编码', '编 号', '编 码',],
        'quantity' => ['消耗量', '数量', '数 量'],
        'unit_price' => ['除税市场价', '要约价不含税市场价'],
        'total_price' => ['要约价不含税市场价合计', '除税市场价合计'],
        'tax_unit_price' => ['市场价', '要约价含税市场价', '单价', '单价(元)'],
        'tax_total_price' => ['市场价合计', '要约价含税市场价合计', '合计', '合价(元)'],
        'unit' => ['单位', '单 位'],
        'description' => ['规格型号'],
        'catename' => ['类别', '分类'],
        'tax' => ['进项税率', '税率', '税率%'],
        'suppliername' => ['厂家', '厂 家'],
        'brandname' => ['品牌', '品 牌', ],
        'price_source' => ['价格来源'],
    ];
    //读excel文件每次限制行数
    protected $excelLimit = 1500;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\ProjectRencaiji;
        $config = DB::name('config')->where('group', 'caijiaku')->column('value', 'name');
        $config = array_map(function ($item) {
            return json_decode($item, true);
        }, $config);
        $this->projectConfig = $config;
        $this->view->assign('projectConfig', $this->projectConfig);
        $this->assignconfig('projectConfig', $config);
        $action = strtolower($this->request->action());
        if (in_array($action, ['catelist', 'index', 'getlistbyproject'])) {
            $this->dataLimit = false;
        } else {
            $this->dataLimit = 'auth';
        }
    }



    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */

     /**
     * 查看
     *
     * @return string|Json
     * @throws \think\Exception
     * @throws DbException
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if (false === $this->request->isAjax()) {
            return $this->view->fetch();
        }
        //如果发送的来源是 Selectpage，则转发到 Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        $wheres = [];
        //排除超管数据
        if(!$this->auth->isSuperAdmin()) {
            //排除超级管理员
            $groupWhere = [
                'caijiakuid' => CAIJIAKU_ID,
                'status' => 'normal',
                'rules' => '*',
            ];
            $groupIds = Db::name('auth_group')->where($groupWhere)->column('id');
            $adminIds = Db::name('auth_group_access')->where(['group_id' => ['in', $groupIds]])->column('uid');
            $wheres['project_rencaiji.admin_id'] = ['not in', $adminIds];
        }
        $list = $this->model
            ->with(['admin', 'project'])
            ->where($where)
            ->where($wheres)
            ->order($sort, $order)
            ->paginate($limit);
            foreach ($list as $row) {
                $row->getRelation('project')->visible(['project_name','project_address','pricing_date','project_class','project_bulid_cost','project_scale','square_build_cost','build_unit','construction_unit','structure_type','build_cost_type','build_form','taxation_type','contract_no','contract_name','createtime']);
                $row->getRelation('admin')->visible(['nickname']);
                // $row->visible(['id','admin_id','project_name','project_address','pricing_date','project_class','project_bulid_cost','project_scale','square_build_cost','build_unit','construction_unit','structure_type','build_cost_type','build_form','taxation_type','contract_no','contract_name','createtime', 'admin.nickname', 'updatetime']);
            }
        $result = ['total' => $list->total(), 'rows' => $list->items()];
        return json($result);
    }

    public function getListByProject()
    {
        $ids = $this->request->param("id");
        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        $list = $this->model
            ->where(['project_id' => $ids])
            ->where($where)
            ->order($sort, $order)
            ->paginate($limit);
            foreach ($list as $row) {
                $row->visible(['id','name','code','catename','description','quantity','unit','unit_price','total_price','tax','tax_unit_price','tax_total_price','createtime','updatetime', 'isimport', 'brandname', 'suppliername', 'price_source']);
            }
        $result = ['total' => $list->total(), 'rows' => $list->items()];

        return json($result);
    }

    public function detail($ids = null)
    {
        $row = $this->model->where(['project_rencaiji.id' => $ids])->with([
            'admin','project'
        ])->find();
        if (!$row) {
            $this->error(__('No Results were found'));
        }
        $row = $row->toArray();
        $projectFiles = $row['project']['project_files'] ? explode(',', $row['project']['project_files']) : [];
        $row['project']['project_files_arr'] = array_map(function ($item) {
            return cdnurl($item);
        }, $projectFiles);
        $contractFiles = $row['project']['contract_files'] ? explode(',', $row['project']['contract_files']) : [];
        $row['project']['contract_files_arr'] = array_map(function ($item) {
            return cdnurl($item);
        }, $contractFiles);
        $preparedFiles = $row['project']['prepared_files'] ? explode(',', $row['project']['prepared_files']) : [];
        $row['project']['prepared_files_arr'] = array_map(function ($item) {
            return cdnurl($item);
        }, $preparedFiles);
        $this->view->assign('row', $row);
        $this->view->assign('ids', $ids);
        return $this->view->fetch();
    }

    /**
     * 添加
     *
     * @return string
     * @throws \think\Exception
     */
    public function add()
    {
        $project_id = $this->request->param('project_id');
        if (false === $this->request->isPost()) {
            $this->view->assign('project_id', $project_id);
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $params = $this->preExcludeFields($params);

        if ($this->dataLimit && $this->dataLimitFieldAutoFill) {
            $params[$this->dataLimitField] = $this->auth->id;
        }
        
        $result = false;
        Db::startTrans();
        try {
            //是否采用模型验证
            if ($this->modelValidate) {
                $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.add' : $name) : $this->modelValidate;
                $this->model->validateFailException()->validate($validate);
            }
            $result = $this->model->allowField(true)->save($params);
            Db::commit();
        } catch (ValidateException|PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($result === false) {
            $this->error(__('No rows were inserted'));
        }
        $this->success();
    }

    /**
     * 编辑
     *
     * @param $ids
     * @return string
     * @throws DbException
     * @throws \think\Exception
     */
    public function edit($ids = null)
    {
        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds) && !in_array($row[$this->dataLimitField], $adminIds)) {
            $this->error(__('You have no permission'));
        }
        if (false === $this->request->isPost()) {
            $this->view->assign('row', $row);
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $params = $this->preExcludeFields($params);
        $result = false;
        Db::startTrans();
        try {
            //是否采用模型验证
            if ($this->modelValidate) {
                $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.edit' : $name) : $this->modelValidate;
                $row->validateFailException()->validate($validate);
            }
            $result = $row->allowField(true)->save($params);
            Db::commit();
        } catch (ValidateException|PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if (false === $result) {
            $this->error(__('No rows were updated'));
        }
        $this->success();
    }

    public function import()
    {
        $project_id = $this->request->param('project_id');
        if ($this->request->isPost() == false) {
            $projectInfo = Project::find($project_id);
            $this->view->assign('info', $projectInfo);
            $this->view->assign('excelTitleMapping', $this->excelTitleMapping);
            return $this->view->fetch();
        }
        set_time_limit(0);
        $file = $this->request->request('file', '');
        $curtimes = $this->request->request("curtimes/d", 0);//分批量请求
        $lastrow = $this->request->request("lastrow/d", 0);//上次最后读的行数
        $project_id = $this->request->request("project_id/d", 0);//上次最后读的行数
        if (empty($file)) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        if (empty($project_id)) {
            $this->error(__('Parameter %s can not be empty', 'project_id'));
        }
        $filePath = ROOT_PATH . DS .'public'. $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        if (!file_exists($filePath)) {
            $this->error('文件不存在');
        }

        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, "w");
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding != 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }


        if (!$PHPExcel = $reader->load($filePath)) {
            $this->error(__('Unknown data format'));
        }
        $currentSheet = $PHPExcel->getSheet(0);
        $allColumn = $currentSheet->getHighestDataColumn();
        $allRow = $currentSheet->getHighestRow();
        $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
        $limit = $this->excelLimit;
        $firstnum = $lastrow + 1;
        $fields = [];
        $nameField = $codeField = $unitField = $quantityField = $unitPriceField = $totalPriceField =  $descriptionField = $taxField = $taxUnitPriceField = $taxTotalPriceField = $catenameField = $brandnameField = $suppliernameField = $priceSourceField = '';
        $isStart = false;//是否开始读取数据
        $isFirstLine = false;//是否第一行就是表格标题
        //获取标题
        for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
            $values = [];
            for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                $values[] = is_null($val) ? '' : trim($val);
            }
            //判断当前行是否列表开始
            if (!$isStart) {
                //判断是否有标题
                $nameMapping = array_intersect($values, $this->excelTitleMapping['name']);
                if (!empty($nameMapping)) {
                    //判断是否有标题
                    $nameMapping = array_intersect($values, $this->excelTitleMapping['name']);
                    if (!empty($nameMapping)) {
                        $isStart = true;
                        $mergeMapping = [];//array_intersect($values, $this->mergeTitle);
                        if (!empty($mergeMapping)) {
                            $currentRow += 1;
                        }
                        if (empty($fields)) {
                            $isFirstLine = $currentRow == 1 ? true : false;
                            $fields = $values;
                            //获取二级表头
                            if (!empty($mergeMapping)) {
                                //获取mergeMapping 第一个key
                                $key = array_keys($mergeMapping)[0];
                                //将二级表头替换掉一级表头
                                for ($currentColumn = $key+1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                                    $fields[$currentColumn - 1] = is_null($val) ? '' : trim($val);
                                }
                            }
                            $fields = array_map(function ($item) {
                                return $this->trimall($item);
                            }, $fields);
                            // var_dump($fields);
                            //数组交集
                            $nameMapping = array_intersect($fields, $this->excelTitleMapping['name']);
                            $codeMapping = array_intersect($fields, $this->excelTitleMapping['code']);
                            $catenameMapping = array_intersect($fields, $this->excelTitleMapping['catename']);
                            $descriptionMapping = array_intersect($fields, $this->excelTitleMapping['description']);
                            $unitMapping = array_intersect($fields, $this->excelTitleMapping['unit']);
                            $quantityMapping = array_intersect($fields, $this->excelTitleMapping['quantity']);
                            $unitPriceMapping = array_intersect($fields, $this->excelTitleMapping['unit_price']);
                            $totalPriceMapping = array_intersect($fields, $this->excelTitleMapping['total_price']);
                            $taxMapping = array_intersect($fields, $this->excelTitleMapping['tax']);
                            $taxUnitPriceMapping = array_intersect($fields, $this->excelTitleMapping['tax_unit_price']);
                            $taxTotalPriceMapping = array_intersect($fields, $this->excelTitleMapping['tax_total_price']);
                            $brandnameMapping = array_intersect($fields, $this->excelTitleMapping['brandname']);
                            $suppliernameMapping = array_intersect($fields, $this->excelTitleMapping['suppliername']);
                            $priceSourceMapping = array_intersect($fields, $this->excelTitleMapping['price_source']);
                            // var_dump(empty($nameMapping)); var_dump(empty($unitMapping)); var_dump(empty($unitPriceMapping));
                            if(empty($nameMapping) || empty($unitMapping) || (empty($unitPriceMapping) && empty($taxUnitPriceMapping))){
                                $this->error(__('文档识别失败，请确保文档中必须包含：'.implode('/',$this->excelTitleMapping['name']).'，'.implode('/',$this->excelTitleMapping['unit_price']).'，'.implode('/',$this->excelTitleMapping['unit']).'三个必填信息'));
                            }
                            $nameField = array_values($nameMapping)[0];
                            $codeField = array_values($codeMapping)[0];
                            $catenameField = array_values($catenameMapping)[0];
                            $descriptionField = array_values($descriptionMapping)[0];
                            $unitField = array_values($unitMapping)[0];
                            $quantityField = !empty($quantityMapping) ? array_values($quantityMapping)[0] : '';
                            $unitPriceField = !empty($unitPriceMapping) ? array_values($unitPriceMapping)[0] : '';
                            $totalPriceField = !empty($totalPriceMapping) ? array_values($totalPriceMapping)[0] : '';
                            $taxField = !empty($taxMapping) ? array_values($taxMapping)[0] : '';
                            $taxUnitPriceField = !empty($taxUnitPriceMapping) ? array_values($taxUnitPriceMapping)[0] : '';
                            $taxTotalPriceField = !empty($taxTotalPriceMapping) ? array_values($taxTotalPriceMapping)[0] : '';
                            $brandnameField = !empty($brandnameMapping) ? array_values($brandnameMapping)[0] : '';
                            $suppliernameField = !empty($suppliernameMapping) ? array_values($suppliernameMapping)[0] : '';
                            $priceSourceField = !empty($priceSourceMapping) ? array_values($priceSourceMapping)[0] : '';
                        }
                    } else {
                        continue;
                    }
                } else {
                    continue;
                }
            }
        }
        $data = [];
        for ($currentRow = $firstnum; $currentRow <= $allRow; $currentRow++) {
            $lastrow = $currentRow;
            if (count($data) >= $limit) {
                break;
            }
            $values = [];
            for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                $values[] = is_null($val) ? '' : trim($val);
            }
            $isStart = true;
            $temp = array_combine($fields, $values);
            
            if(empty($temp[$nameField]) || $temp[$nameField] == $nameField || empty($temp[$unitField]) || $temp[$unitField] == $unitField){
                continue;
            }

            $row = [];
            $row['caijiakuid'] = CAIJIAKU_ID;
            $row['admin_id'] = $this->auth->id;
            $row['project_id'] = $project_id;
            $row['name'] = $temp[$nameField] ?? '';
            $row['code'] = $temp[$codeField] ?? '';
            $row['catename'] = $temp[$catenameField] ?? '';
            $row['description'] = $temp[$descriptionField] ?? '';
            $row['quantity'] = $quantityField ? $temp[$quantityField] : '';
            $row['unit'] = $temp[$unitField] ?? '';
            $row['unit_price'] = $unitPriceField ? $temp[$unitPriceField] : 0;
            $row['total_price'] = $totalPriceField && !empty($temp[$totalPriceField]) ? $temp[$totalPriceField] : round($row['quantity'] * $row['unit_price'], 2);
            $row['tax'] = $taxField ? $temp[$taxField] : 0;
            $row['tax_unit_price'] = $taxUnitPriceField ? $temp[$taxUnitPriceField] : 0;
            $row['tax_total_price'] = $taxTotalPriceField && !empty($temp[$taxTotalPriceField]) ? $temp[$taxTotalPriceField] : round($row['quantity'] * $row['tax_unit_price'], 2);
            $row['brandname'] = $brandnameField ? $temp[$brandnameField] : '';
            $row['suppliername'] = $suppliernameField ? $temp[$suppliernameField] : '';
            $row['price_source'] = $priceSourceField ? $temp[$priceSourceField] : '';
            $row['createtime'] = time();
            $row['updatetime'] = time();
            $data[$currentRow] = $row;
        }
        if ($data) {
            $data = array_values($data);
            Db::startTrans();
            try{
                $chunkSize = 200; // 每次插入的数据条数
                $dataChunks = array_chunk($data, $chunkSize); // 将数据分割成小数组
                foreach ($dataChunks as $chunk) {
                    $res = Db::name('project_rencaiji')->insertAll($chunk); // 分批插入数据
                }
                Db::commit();
            } catch (\Exception $e) {
                Db::rollback();
                $this->error($e->getMessage());
            }
            $this->success('成功导入数据:' . count($data) . '条', '', ['lastrow' => $lastrow]);
        } else {
            $this->error('没有数据可导入');
        }
    }

    public function import_preview()
    {
        $file = $this->request->request('listfile');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, "w");
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding != 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //加载文件
        if (!$PHPExcel = $reader->load($filePath)) {
            $this->error(__('Unknown data format'));
        }
        $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
        $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
        $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
        $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
        $fields = [];
        $newrowdata = [];
        $nameField = $codeField = $unitField = $quantityField = $unitPriceField = $totalPriceField =  $descriptionField = $taxField = $taxUnitPriceField = $taxTotalPriceField = $catenameField = $brandnameField = $suppliernameField = $priceSourceField = '';
        $isStart = false;//是否开始读取数据
        $isFirstLine = false;//是否第一行就是表格标题
        for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
            $values = [];
            for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                $values[] = is_null($val) ? '' : trim($val);
            }
            // var_dump($values);
            // 判断当前行是否列表开始
            if (!$isStart) {
                //判断是否有标题
                $nameMapping = array_intersect($values, $this->excelTitleMapping['name']);
                if (!empty($nameMapping)) {
                    $isStart = true;
                    $mergeMapping = [];//array_intersect($values, $this->mergeTitle);
                    // if (!empty($mergeMapping)) {
                    //     $currentRow += 1;
                    // }
                    if (empty($fields)) {
                        $isFirstLine = $currentRow == 1 ? true : false;
                        $fields = $values;
                        //获取二级表头
                        if (!empty($mergeMapping)) {
                            //获取mergeMapping 第一个key
                            $key = array_keys($mergeMapping)[0];
                            //将二级表头替换掉一级表头
                            for ($currentColumn = $key+1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                                $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                                $fields[$currentColumn - 1] = is_null($val) ? '' : trim($val);
                            }
                        }
                        $fields = array_map(function ($item) {
                            return $this->trimall($item);
                        }, $fields);
                        // var_dump($fields);
                        //数组交集
                        $nameMapping = array_intersect($fields, $this->excelTitleMapping['name']);
                        $codeMapping = array_intersect($fields, $this->excelTitleMapping['code']);
                        $catenameMapping = array_intersect($fields, $this->excelTitleMapping['catename']);
                        $descriptionMapping = array_intersect($fields, $this->excelTitleMapping['description']);
                        $unitMapping = array_intersect($fields, $this->excelTitleMapping['unit']);
                        $quantityMapping = array_intersect($fields, $this->excelTitleMapping['quantity']);
                        $unitPriceMapping = array_intersect($fields, $this->excelTitleMapping['unit_price']);
                        $totalPriceMapping = array_intersect($fields, $this->excelTitleMapping['total_price']);
                        $taxMapping = array_intersect($fields, $this->excelTitleMapping['tax']);
                        $taxUnitPriceMapping = array_intersect($fields, $this->excelTitleMapping['tax_unit_price']);
                        $taxTotalPriceMapping = array_intersect($fields, $this->excelTitleMapping['tax_total_price']);
                        $brandnameMapping = array_intersect($fields, $this->excelTitleMapping['brandname']);
                        $suppliernameMapping = array_intersect($fields, $this->excelTitleMapping['suppliername']);
                        $priceSourceMapping = array_intersect($fields, $this->excelTitleMapping['price_source']);
                        // var_dump(empty($nameMapping)); var_dump(empty($unitMapping)); var_dump(empty($unitPriceMapping));
                        if(empty($nameMapping) || empty($unitMapping) || empty($unitPriceMapping)){
                            $this->error(__('文档识别失败，请确保文档中必须包含：'.implode('/',$this->excelTitleMapping['name']).'，'.implode('/',$this->excelTitleMapping['unit_price']).'，'.implode('/',$this->excelTitleMapping['unit']).'三个必填信息'));
                        }
                        $nameField = array_values($nameMapping)[0];
                        $codeField = array_values($codeMapping)[0];
                        $catenameField = array_values($catenameMapping)[0];
                        $descriptionField = array_values($descriptionMapping)[0];
                        $unitField = array_values($unitMapping)[0];
                        $quantityField = !empty($quantityMapping) ? array_values($quantityMapping)[0] : '';
                        $unitPriceField = !empty($unitPriceMapping) ? array_values($unitPriceMapping)[0] : '';
                        $totalPriceField = !empty($totalPriceMapping) ? array_values($totalPriceMapping)[0] : '';
                        $taxField = !empty($taxMapping) ? array_values($taxMapping)[0] : '';
                        $taxUnitPriceField = !empty($taxUnitPriceMapping) ? array_values($taxUnitPriceMapping)[0] : '';
                        $taxTotalPriceField = !empty($taxTotalPriceMapping) ? array_values($taxTotalPriceMapping)[0] : '';
                        $brandnameField = !empty($brandnameMapping) ? array_values($brandnameMapping)[0] : '';
                        $suppliernameField = !empty($suppliernameMapping) ? array_values($suppliernameMapping)[0] : '';
                        $priceSourceField = !empty($priceSourceMapping) ? array_values($priceSourceMapping)[0] : '';
                    }
                } else {
                    continue;
                }
            } else {
                $data = [];
                $temp = array_combine($fields, $values);
                // if (in_array($temp[$nameField], $this->excludeTitle)) continue;
                // if(empty($temp[$nameField]) || empty($temp[$unitPriceField]) || empty($temp[$unitField])){
                if(empty($temp[$nameField])){
                    $isStart = $isFirstLine ? true : false;
                } else {
                    $data['name'] = $temp[$nameField] ?? '';
                    $data['code'] = $temp[$codeField] ?? '';
                    $data['catename'] = $temp[$catenameField] ?? '';
                    $data['description'] = $temp[$descriptionField] ?? '';
                    $data['quantity'] = $quantityField ? $temp[$quantityField] : '';
                    $data['unit'] = $temp[$unitField] ?? '';
                    $data['unit_price'] = $unitPriceField ? $temp[$unitPriceField] : '-';
                    $data['total_price'] = $totalPriceField ? $temp[$totalPriceField] : '-';
                    $data['tax'] = $taxField ? $temp[$taxField] : '-';
                    $data['tax_unit_price'] = $taxUnitPriceField ? $temp[$taxUnitPriceField] : '-';
                    $data['tax_total_price'] = $taxTotalPriceField ? $temp[$taxTotalPriceField] : '-';
                    $data['brandname'] = $brandnameField ? $temp[$brandnameField] : '-';
                    $data['suppliername'] = $suppliernameField ? $temp[$suppliernameField] : '-';
                    $data['price_source'] = $priceSourceField ? $temp[$priceSourceField] : '-';
                    $data['hang'] = $currentRow;
                    $newrowdata[$currentRow] = $data;
                }
            }
        }
        
        if(count($newrowdata)==0){
            $this->error(__('文档识别失败，文档数据为空'));
        }
        $newrowdata = array_values($newrowdata);
        $return = [
            'data' => $newrowdata,
            'count' => count($newrowdata),
            'limit' => $this->excelLimit,
            'batchtimes' => ceil(count($newrowdata) / $this->excelLimit)
        ];
        $this->success('导入成功', '', $return);
    }

    //删除空格
    protected function trimall($str)
    {
        $oldchar = array(" ", "　", "\t", "\n", "\r");
        $newchar = array("", "", "", "", "");
        $str = str_replace($oldchar, $newchar, $str);
        $str =  preg_replace("/(\s|\&nbsp\;|　|\xc2\xa0)/", "", $str);
        return $str;
    }

    public function catelist()
    {
        //设置过滤方法
        $this->request->filter(['trim', 'strip_tags', 'htmlspecialchars']);

        //搜索关键词,客户端输入以空格分开,这里接收为数组
        $word = (array)$this->request->request("q_word/a");
        //当前页
        $page = $this->request->request("pageNumber");
        //分页大小
        $pagesize = $this->request->request("pageSize");
        //搜索条件
        $andor = $this->request->request("andOr", "and", "strtoupper");
        //排序方式
        $orderby = (array)$this->request->request("orderBy/a");
        //显示的字段
        $field = $this->request->request("showField");
        //主键
        $primarykey = $this->request->request("keyField");
        //主键值
        $primaryvalue = $this->request->request("keyValue");
        //搜索字段
        $searchfield = (array)$this->request->request("searchField/a");
        //自定义搜索条件
        $custom = (array)$this->request->request("custom/a");
        //如果有primaryvalue,说明当前是初始化传值
        if ($primaryvalue !== null) {
            $where = [$primarykey => ['in', $primaryvalue]];
            $pagesize = 999999;
        } else {
            $where = function ($query) use ($word, $andor, $field, $searchfield, $custom) {
                $logic = $andor == 'AND' ? '&' : '|';
                $searchfield = is_array($searchfield) ? implode($logic, $searchfield) : $searchfield;
                $searchfield = str_replace(',', $logic, $searchfield);
                $word = array_filter(array_unique($word));
                if (count($word) == 1) {
                    $query->where($searchfield, "like", "%" . reset($word) . "%");
                } else {
                    $query->where(function ($query) use ($word, $searchfield) {
                        foreach ($word as $index => $item) {
                            $query->whereOr(function ($query) use ($item, $searchfield) {
                                $query->where($searchfield, "like", "%{$item}%");
                            });
                        }
                    });
                }
                if ($custom && is_array($custom)) {
                    foreach ($custom as $k => $v) {
                        if (is_array($v) && 2 == count($v)) {
                            $query->where($k, trim($v[0]), $v[1]);
                        } else {
                            $query->where($k, '=', $v);
                        }
                    }
                }
            };
        }
        $adminids = $this->auth->getChildrenAdminIds(true);
        $adminids = $adminids ? $adminids : [$this->auth->id];
        $wheres = [
            'admin_id' => ['in', $adminids],
            'catename' => ['<>', ''],
            // 'caijiakuid' => CAIJIAKU_ID
        ];
        $total = $this->model->where($wheres)->where($where)->group('catename')->count();
        $list = $this->model->field('id,catename')->where($wheres)->where($where)->group('catename')->page($page, $pagesize)->select();
        // echo $this->model->getLastSql();
        return json(['list' => $list, 'total' => $total]);
    }

    /**
     * 导入材价库
     *
     * @return void
     */
    public function importformal()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            set_time_limit(0);
            $type = $this->request->request('type');//1当前页  2按项目分类
            if ($type == 1) {
                $ids = $this->request->request('ids');
                $isall = $this->request->request('isall');
                if($isall != 3 ){
                    if (empty($ids)) {
                        $this->error(__('请选择要导入的数据'));
                    }
                    $list = $this->model->where('isimport', 0)->where(['id' => ['in', $ids]])->select();
                    if (empty($list)) {
                        $this->error(__('无导入数据'));
                    }
                } else {
                    list($where, $sort, $order, $offset, $limit) = $this->buildparams();
                    $list = $this->model->where('isimport', 0)->where($where)->select();
                    if (empty($list)) {
                        $this->error(__('无导入数据'));
                    }
                }

            } else {
                $project_id = $this->request->request('project_id');
                $catename = $this->request->request('catename');
                if (empty($project_id)) {
                    $this->error(__('请选择要导入的项目'));
                }
                $where = function ($query) use ($project_id, $catename) {
                    if ($project_id) {
                        $query->where('project_id', $project_id);
                    }
                    if ($catename) {
                        $query->where(['catename' => ['in', $catename]]);
                    }
                };
                $list = $this->model->where('isimport', 0)->where($where)->select();
                if (empty($list)) {
                    $this->error(__('无导入数据'));
                }
            }
            $list = collection($list)->toArray();
            // echo $this->model->getLastSql();
            // var_dump($list);
            $es = ClientBuilder::create()->setHosts($this->eshost)->build();
            
            $res = $this->model->importFormal($list, $this->auth->id, $es);
            if ($res['status'] == 0) {
                $this->error($res['msg']);
            } else {
                $this->success('导入成功','', $list);
            }
        }
        return $this->view->fetch();

    }
}
